# Created by yang

import xlrd


class WorkBook(object):
    def __init__(self, filepath):
        self.workbook = xlrd.open_workbook(filepath)
        self._sheet = ''

    def sheet_by_name(self, sheet_name):
        self._sheet = self.workbook.sheet_by_name(sheet_name)
        return self._sheet

    def sheet(self, index=0):
        """根据index获取sheet"""
        self._sheet = self.workbook.sheet_by_index(index)
        return self._sheet


class Sheet:
    def __init__(self, sheet):
        self.sheet = sheet

    @property
    def rows_count(self):
        """
        :return:返回总行数 
        """
        return self.sheet.nrows

    @property
    def cols_count(self):
        """
        :return:返回总列数 
        """
        return self.sheet.ncols

    @property
    def rows(self):
        """
        :return: a generator for iterating through each row.
        """
        return self.sheet.get_rows()

    def get_row_value(self, n):
        """
        :param rowx: 行数
        :return: 返回第n行数据,注意:从第0行开始
        """
        return self.sheet.row_values(n)

    def get_col_value(self, n):
        """
        :param rowx: 列数
        :return: 返回第n列数据,注意:从第0列开始
        """
        return self.sheet.col_values(n)

    def get_row_by_value(self, value, col, title_row=0):
        """
        根据给定的value,获取出该row,该给定的value需具有唯一性
        :param value: 
        :param col: 该value所在的列,从0列开始
        :param title_row: 表头所在的行
        :return: 
        """
        titles = self.sheet.row_values(title_row)

        cols = self.sheet.col_values(col)
        for i, v in enumerate(cols):
            if value == v.strip():
                values = self.sheet.row_values(i)
                return dict(zip(titles, values))
        else:
            raise Exception("传入的value错误,未找到匹配项,请检查:{}".format(value))


def get_row_by_value(sheet, value, col, title_row=0):
    """
    根据给定的value,获取出该row,该给定的value需具有唯一性
    :param sheet: sheet对象
    :param value: 
    :param col: 该value所在的列,从0列开始
    :param title_row: 表头所在的行
    :return: 
    """

    titles = sheet.sheet.row_values(title_row)

    cols = sheet.sheet.col_values(col)
    for i, v in enumerate(cols):
        if value == v:
            values = sheet.sheet.row_values(i)
            return dict(zip(titles, values))
    else:
        raise Exception("传入的value错误,未找到匹配项,请检查:{}".format(value))


if __name__ == '__main__':
    path = 'a.xls'
    obj = Sheet(WorkBook(path).sheet())
    for i, o in enumerate(obj.rows):
        # 这是我添加的数据
        a, b, c, *_ = o
        print(i, a.value, b.value, c.value)
